﻿Imports System.Data
Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class ProgramaViajeADO
    Dim MiConexion As New ConexionADO
    Dim cnx As New SqlConnection(MiConexion.GetCnx)
    Dim cmd As New SqlCommand
    Dim lExito As Boolean

    Public Function CargarTipoVueloCupos(ByVal sCodTipo As String) As Integer
        Dim dtr As SqlDataReader
        Dim nCupos As New Integer
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT NroCupos as cupos FROM dbo.M_TipoVuelo WHERE (CodTipo='{0}')", sCodTipo)
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                nCupos = dtr(0)
            Else
                nCupos = 0
            End If
        Catch x As SqlException
            MsgBox("Programa de Viaje (pr_TraerProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return nCupos
    End Function

    Public Function CrearProgramaViaje(ByVal pvBE As V_ProgramaViajeBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_InsertarProgramaViaje"
        Try
            With pvBE
                cmd.Parameters.Add(New SqlParameter("@psCodContrataViaje", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodContrataViaje").Value = .CodContrataViaje
                cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
                cmd.Parameters("@psCodViaje").Value = .CodViaje
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@psCodEmpresa", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodEmpresa").Value = .CodEmpresa
                cmd.Parameters.Add(New SqlParameter("@psCodTipoVuelo", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodTipoVuelo").Value = .CodTipoVuelo
                cmd.Parameters.Add(New SqlParameter("@pnNroCupos", SqlDbType.Int))
                cmd.Parameters("@pnNroCupos").Value = .NroCupos
                cmd.Parameters.Add(New SqlParameter("@psCodLugarOrigen", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarOrigen").Value = .CodLugarOrigen
                cmd.Parameters.Add(New SqlParameter("@psCodLugarDestino", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarDestino").Value = .CodLugarDestino
                cmd.Parameters.Add(New SqlParameter("@pnPsoMaxViaje", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoMaxViaje").Value = .PsoMaxViaje
                cmd.Parameters.Add(New SqlParameter("@psObsViaje", SqlDbType.Text))
                cmd.Parameters("@psObsViaje").Value = .ObsViaje
                cmd.Parameters.Add(New SqlParameter("@psEstadoViaje", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoViaje").Value = .EstadoViaje
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("Programa de Viaje (pr_InsertarProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function EliminarProgramaViaje(ByVal sCodpv As String) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_EliminarProgramaViaje"
        Try
            cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
            cmd.Parameters("@psCodViaje").Value = sCodpv
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("Programa de Viaje (pr_EliminarProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function ListarProgramaViaje(ByVal sFiltro As String) As DataTable
        Dim dt As New DataTable
        Const sql As String = "SELECT pv.CodContrataViaje,co.desContrataViaje,pv.CodViaje,pv.FchViaje,pv.Codempresa,ca.NomEmpresa,pv.NroCupos,pv.CodLugarOrigen,lo.NomLugar AS DesOrigen,pv.CodLugarDestino,ld.Nomlugar AS DesDestino,pv.PsoMaxViaje,pv.ObsViaje,ev.desEstadoVuelo FROM dbo.V_ProgramaViaje pv INNER JOIN dbo.M_EmpresaTransporte ca ON pv.CodEmpresa=ca.CodEmpresa INNER JOIN dbo.M_lugar lo ON pv.CodLugarOrigen=lo.CodLugar INNER JOIN dbo.M_Lugar ld ON pv.CodLugarDestino=ld.CodLugar INNER JOIN dbo.M_Contratante co ON pv.codContrataViaje=co.codContrataViaje INNER JOIN dbo.M_Estado_Vuelo ev ON pv.EstadoViaje=ev.codEstadoVuelo ORDER BY fchviaje DESC"
        Dim scriterio As String = IIf(sFiltro.Trim() = "", "", " Where " & Trim(sFiltro))
        Try
            cnx.Open()
            Using da As New SqlDataAdapter(sql & scriterio, cnx)
                da.Fill(dt)
            End Using
            cnx.Close()
            Return dt
        Catch myException As Exception
            MsgBox("Listar Programa de Viaje --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function ListarProgramaViajeTreeList(ByVal p_EstadoViaje As Char) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeList", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_EstadoViaje", SqlDbType.Char)
                    cmd.Parameters("@p_EstadoViaje").Value = p_EstadoViaje
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (ListarProgramaViajeTreeList) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function ListarProgramaViajeTreeListCerrado(ByVal p_EstadoViaje As Char) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListCerrado", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_EstadoViaje", SqlDbType.Char)
                    cmd.Parameters("@p_EstadoViaje").Value = p_EstadoViaje
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (ListarProgramaViajeTreeListCerrado) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function ListarProgramaViajeTreeListChecking(ByVal p_EstadoViaje As Char, ByVal p_CodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListChecking", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_EstadoViaje", SqlDbType.Char)
                    cmd.Parameters("@p_EstadoViaje").Value = p_EstadoViaje
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.VarChar, 20)
                    cmd.Parameters("@p_CodUser").Value = p_CodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (ListarProgramaViajeTreeListChecking) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function ListarProgramaViajeTreeListCheckingCerrado(ByVal p_EstadoViaje As Char, ByVal p_CodUser As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_ListarProgramaViajeTreeListCheckingCerrado", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_EstadoViaje", SqlDbType.Char)
                    cmd.Parameters("@p_EstadoViaje").Value = p_EstadoViaje
                    cmd.Parameters.Add("@p_CodUser", SqlDbType.VarChar, 20)
                    cmd.Parameters("@p_CodUser").Value = p_CodUser
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVTree")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (ListarProgramaViajeTreeListCheckingCerrado) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function ModificarProgramaViaje(ByVal pvBE As V_ProgramaViajeBE) As Boolean
        cmd.Connection = cnx
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_ModificarProgramaViaje"
        Try
            With pvBE
                cmd.Parameters.Add(New SqlParameter("@psCodContrataViaje", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodContrataViaje").Value = .CodContrataViaje
                cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
                cmd.Parameters("@psCodViaje").Value = .CodViaje
                cmd.Parameters.Add(New SqlParameter("@pdFchViaje", SqlDbType.DateTime))
                cmd.Parameters("@pdFchViaje").Value = .FchViaje
                cmd.Parameters.Add(New SqlParameter("@psCodEmpresa", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodEmpresa").Value = .CodEmpresa
                cmd.Parameters.Add(New SqlParameter("@psCodTipoVuelo", SqlDbType.VarChar, 2))
                cmd.Parameters("@psCodTipoVuelo").Value = .CodTipoVuelo
                cmd.Parameters.Add(New SqlParameter("@pnNroCupos", SqlDbType.Int))
                cmd.Parameters("@pnNroCupos").Value = .NroCupos
                cmd.Parameters.Add(New SqlParameter("@psCodLugarOrigen", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarOrigen").Value = .CodLugarOrigen
                cmd.Parameters.Add(New SqlParameter("@psCodLugarDestino", SqlDbType.VarChar, 3))
                cmd.Parameters("@psCodLugarDestino").Value = .CodLugarDestino
                cmd.Parameters.Add(New SqlParameter("@pnPsoMaxViaje", SqlDbType.Decimal))
                cmd.Parameters("@pnPsoMaxViaje").Value = .PsoMaxViaje
                cmd.Parameters.Add(New SqlParameter("@psObsViaje", SqlDbType.Text))
                cmd.Parameters("@psObsViaje").Value = .ObsViaje
                cmd.Parameters.Add(New SqlParameter("@psEstadoViaje", SqlDbType.VarChar, 1))
                cmd.Parameters("@psEstadoViaje").Value = .EstadoViaje
            End With
            cnx.Open()
            cmd.ExecuteNonQuery()
            lExito = True
        Catch x As SqlException
            MsgBox("Programa de Viaje (pr_ModificarProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
            lExito = False
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return lExito
    End Function

    Public Function NuevoCorrelativo(ByVal sNomLugar As String) As String
        Dim sDia, sMes, sAno, sBus, sNewCod, sCodAct As String
        sAno = Microsoft.VisualBasic.Right(Date.Today, 4)
        sMes = Microsoft.VisualBasic.Mid(Date.Today, 4, 2)
        sDia = Microsoft.VisualBasic.Left(Date.Today, 2)
        sBus = String.Format("{0}{1}{2}" & sNomLugar, sAno, sMes, sDia)
        Try
            cnx.Open()
            With cmd
                .Connection = cnx
                .CommandType = CommandType.Text
                .CommandText = String.Format("SELECT TOP 1 RIGHT(CodViaje,3) FROM dbo.V_ProgramaViaje WHERE LEFT(CodViaje ,9)='{0}' ORDER BY CodViaje DESC", sBus)
                sCodAct = .ExecuteScalar()
            End With
            If sCodAct <> "" Then
                sNewCod = sBus & Right(CStr(Val(sCodAct) + 10001), 3)
            Else
                sNewCod = sBus & "001"
            End If
        Catch myException As Exception
            MsgBox("Correlativo --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        Finally
            cnx.Close()
        End Try
        Return sNewCod
    End Function

    Public Function sp_Lista_ProgramaViaje(ByVal p_CodLugarOrigen As String) As DataTable
        Try
            Dim cn As New SqlConnection(MiConexion.GetCnx)
            Using cn
                Dim cmd As New SqlCommand("sp_Lista_ProgramaViaje", cn)
                Using cmd
                    cmd.CommandType = Data.CommandType.StoredProcedure
                    cmd.Parameters.Add("@p_CodLugarOrigen", SqlDbType.Char, 3)
                    cmd.Parameters("@p_CodLugarOrigen").Value = p_CodLugarOrigen
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable("PVList")
                    da.Fill(dt)
                    Return dt
                End Using
            End Using
        Catch myException As Exception
            MsgBox("ProgramaViajeADO (sp_Lista_ProgramaViaje) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function TraerProgramaViaje(ByVal sCodpv As String) As V_ProgramaViajeBE
        Dim dtr As SqlDataReader
        Dim _pvBE As New V_ProgramaViajeBE
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "pr_TraerProgramaViaje"
            cmd.Parameters.Add(New SqlParameter("@psCodViaje", SqlDbType.VarChar, 12))
            cmd.Parameters("@psCodViaje").Value = sCodpv
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                With _pvBE
                    .CodContrataViaje = dtr("CodContrataViaje")
                    .CodViaje = dtr("CodViaje")
                    If IsDBNull(dtr("FchViaje")) Then
                        .FchViaje = DateTime.Now
                    Else
                        .FchViaje = dtr("FchViaje")
                    End If
                    .CodEmpresa = dtr("CodEmpresa")
                    .CodTipoVuelo = dtr("CodTipoVuelo")
                    .NroCupos = dtr("NroCupos")
                    .CodLugarOrigen = dtr("CodLugarOrigen")
                    .CodLugarDestino = dtr("CodLugarDestino")
                    .PsoMaxViaje = dtr("PsoMaxViaje")
                    .ObsViaje = dtr("ObsViaje")
                    .EstadoViaje = dtr("EstadoViaje")
                End With
            End If
        Catch x As SqlException
            MsgBox("Programa de Viaje (pr_TraerProgramaViaje) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
            cmd.Parameters.Clear()
        End Try
        Return _pvBE
    End Function
End Class
